﻿using System;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;
using System.Collections.Generic;

namespace PM.Services
{
    public class SecurityServices
    {
        private static Security GetSecurityFromDataReader(IDataReader dataReader)
        {
            Security retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Security();

                retVal.SecurityId = dataReader["SECURITY_ID"].ToString();
                retVal.Name = dataReader["NAME"].ToString();
                double marketValue = 0;
                if (double.TryParse(dataReader["MARKET_VALUE"].ToString(), out marketValue))
                    retVal.MarketValue = marketValue;
                double marginPercentage = 0;
                if (double.TryParse(dataReader["MARGIN_PERCENTAGE"].ToString(), out marginPercentage))
                    retVal.MarginPercentage = marginPercentage;
                DateTime last_mod_date = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["LAST_MOD_DATE"].ToString(), out last_mod_date))
                    retVal.LastModDate = last_mod_date;
            }
            return retVal;
        }

        public static Security GetSecurityById(string securityId)
        {
            Security retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM SECURITY WHERE SECURITY_ID = '" + securityId + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetSecurityFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static Security GetSecurityById(string securityId, OracleConnection con, OracleTransaction tran)
        {
            Security retVal = null;
            //OracleConnection con = new OracleConnection();

            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM SECURITY WHERE SECURITY_ID = '" + securityId + "'";
                cmd.Connection = con;
                cmd.Transaction = tran;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetSecurityFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
            return retVal;
        }

        public static SecurityCollection GetSecurityCollection()
        {
            SecurityCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM SECURITY";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new SecurityCollection();
                    while (dataReader.Read())
                    {
                        Security item = GetSecurityFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static SecurityCollection Get_SecurityID_Name_Collection()
        {
            SecurityCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select t.SECURITY_ID, t.SECURITY_ID || ' - ' || t.NAME " +
                                  "as  SECURITYNAME FROM SECURITY t order by t.SECURITY_ID";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new SecurityCollection();

                    while (dataReader.Read())
                    {

                        Security sec = new Security();
                        sec.SecurityId = dataReader["SECURITY_ID"].ToString();
                        sec.Name = dataReader["SECURITYNAME"].ToString();
                        if (!string.IsNullOrEmpty(sec.SecurityId))
                        {
                            retVal.Add(sec);
                        }

                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        //public static List<string> GetSecSymbolList()
        //{
        //    List<string> retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select SYMBOL from security";
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            retVal = new List<string>();
        //            while (dataReader.Read())
        //            {
        //                string item = dataReader["SYMBOL"].ToString();
        //                if (!string.IsNullOrEmpty(item))
        //                    retVal.Add(item);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}



        public static DataTable GetSecurityCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM SECURITY";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Security security)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO SECURITY(SECURITY_ID,NAME,MARKET_VALUE,MARGIN_PERCENTAGE,LAST_MOD_DATE)" +
                                  "VALUES(:v1,:v2,:v3,:v4,:v5)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = security.SecurityId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = security.Name;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = security.MarketValue;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = security.MarginPercentage;
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = security.LastModDate;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Add(Security security, OracleConnection con, OracleTransaction tran)
        {
            //OracleConnection con = new OracleConnection();

            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO SECURITY(SECURITY_ID,NAME,MARKET_VALUE,MARGIN_PERCENTAGE,LAST_MOD_DATE)" +
                                  "VALUES(:v1,:v2,:v3,:v4,:v5)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = security.SecurityId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = security.Name;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = security.MarketValue;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = security.MarginPercentage;
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = security.LastModDate;

                cmd.Connection = con;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
        }

        public static void Delete(string securityId)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "DELETE FROM SECURITY WHERE SECURITY_ID = '" + securityId + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Security security)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE SECURITY SET " +
                                  "NAME = :v2, " +
                                  "MARKET_VALUE = :v3, " +
                                  "MARGIN_PERCENTAGE = :v4, " +
                                  "LAST_MOD_DATE = :v5 "+
                                  "WHERE SECURITY_ID = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = security.SecurityId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = security.Name;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = security.MarketValue;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = security.MarginPercentage;
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = security.LastModDate;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Security security, OracleConnection con, OracleTransaction tran)
        {
            //OracleConnection con = new OracleConnection();

            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE SECURITY SET " +
                                  "NAME = :v2, " +
                                  "MARKET_VALUE = :v3, " +
                                  "MARGIN_PERCENTAGE = :v4, " +
                                  "LAST_MOD_DATE = :v5 " +
                                  "WHERE SECURITY_ID = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = security.SecurityId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = security.Name;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = security.MarketValue;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = security.MarginPercentage;
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = security.LastModDate;

                cmd.Connection = con;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
        }

        public static void UpdateCollection(SecurityCollection updatedList)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE Security SET " +
                                  "MARKET_VALUE = :v2, " +
                                  "LAST_MOD_DATE = :v3 ";
                cmd.CommandText += " WHERE SECURITY_ID = :v1";

                con.Open();
                foreach (Security security in updatedList)
                {
                    cmd.Parameters.Add(":v2", OracleType.Number).Value = security.MarketValue;
                    cmd.Parameters.Add(":v1", OracleType.VarChar).Value = security.SecurityId;
                    cmd.Parameters.Add(":v3", OracleType.DateTime).Value = security.LastModDate;
                    cmd.Connection = con;
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
